IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsSrvrInstnc' AND type = 'P')
     DROP PROCEDURE dbo.InsSrvrInstnc
GO

CREATE PROCEDURE dbo.InsSrvrInstnc
     @SrvrInstncFullName varchar(255),
     @SrvrInstncTypeId   int,
     @SrvrInstncGrpId    int = 0,
     @SrvrInstncId       int = NULL OUTPUT
AS
/*
 *
 * InsSrvrInstnc
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      SrvrInstncId in both output variable and resultset.
 *
 * Note(s):      
 *
 * CfgCtrl Keys: 
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 04/22/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     SET @SrvrInstncId = NULL

     -- Check parameters.

     -- Check to see if it already exists..
     IF EXISTS ( SELECT 'X' FROM dbo.SrvrInstnc WHERE SrvrInstncFullName = @SrvrInstncFullName )
     BEGIN
          SELECT @SrvrInstncId = SrvrInstncId FROM dbo.SrvrInstnc WHERE SrvrInstncFullName = @SrvrInstncFullName
          
          RAISERROR( 'Specified server instance name already exists.  Cannot add a duplicate.', 16 , 1 )
          RETURN 1
     END
     
     -- Check to see that the TypeId is valid.
     IF NOT EXISTS ( SELECT 'X' FROM dbo.SrvrInstncType WHERE SrvrInstncTypeId = @SrvrInstncTypeId )
     BEGIN
          RAISERROR( 'Specified server instance type does not exist.  Cannot add server instance without a valid type.', 16 , 1 )
          RETURN 1
     END
     
     -- Check to see that the GrpId is valid.
     IF NOT EXISTS ( SELECT 'X' FROM dbo.SrvrInstncGrp WHERE SrvrInstncGrpId = @SrvrInstncGrpId )
     BEGIN
          RAISERROR( 'Specified server instance group does not exist.  Cannot add server instance without a valid group.', 16 , 1 )
          RETURN 1
     END

     -- If you got this far the inputs are valid..  
     BEGIN TRANSACTION

     -- Insert record
     INSERT dbo.SrvrInstnc 
          ( SrvrInstncTypeId,
            SrvrInstncGrpId,
            EnabledFlag,
            SrvrInstncFullName,
            LastModDt )
     SELECT @SrvrInstncTypeId,
            @SrvrInstncGrpId,
            1, -- EnabledFlag
            @SrvrInstncFullName,
            CURRENT_TIMESTAMP

     SELECT @SrvrInstncId = SCOPE_IDENTITY()

     IF ( @SrvrInstncTypeId = 1 ) -- MSSQL
     BEGIN
          INSERT dbo.SrvrInstncMSSQL
               ( SrvrInstncId,
                 SrvrInstncMachineName,
                 SrvrInstncInstanceName )
          SELECT @SrvrInstncId,
                 dbo.GetMachineNameFromMSSQLFullInstanceName( @SrvrInstncFullName ),
                 dbo.GetInstanceNameFromMSSQLFullInstanceName( @SrvrInstncFullName )
     END

     COMMIT

     -- Return resultset
     SELECT SrvrInstncId = @SrvrInstncId 
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Insert a server instance record, this is the basic store procedure for getting nearly all other processes in the repository working.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsSrvrInstnc;
GO
